<?php
//Include Config File and Check User 
include("./include/config.inc.php");
if(!isset($_SESSION['s_activId']))
{
  header('location:checkLogin.php');
}

//Main Varaible
$today = date('Y-m-d');
$regularArray = array();
$i=0;

//Select Type of Loan then Submit 
if(isset($_POST['SubmitForSold']))
{
  $selectRegular = "SELECT loan.loanId, account.mobile, fine, fine AS gold, rate, account.accountName, 
                              itemlocation.itemlocationName, loan.pendingAmount, loan.soldNarration, loan.soldDate
                      FROM loan
                      JOIN itemlocation ON loan.itemlocationId = itemlocation.itemlocationId
                      JOIN account ON account.accountId = loan.accountId";
                      
  //Regular Loan Type Process
  if($_POST['selectLoanType'] == "Regular")
  {
    $selectRegular    = $selectRegular ." WHERE loan.isSold = 'N'";
    $selectRegularRes = mysql_query($selectRegular);
    while($regularRow = mysql_fetch_array($selectRegularRes))
    {
      
      //Pendding Month Process for Regular Loan
  	  $lastInterestMonth       = "SELECT COUNT(isReceive) AS pendingMonth
  	                              FROM interestreceived
  	                             WHERE loanId = '".$regularRow['loanId']."'
  	                               AND isReceive = 0";
  	  $lastInterestMonthResult = mysql_query($lastInterestMonth);
  	  $lastInterestMonthRow    = mysql_fetch_array($lastInterestMonthResult);
  	  
  	  //Pendding Interest Process for Regular Loan 
    	$selectPandingInterest    = "SELECT COUNT(*) AS totalPendingInterest
                                     FROM interestreceived
                                    WHERE loanId = '".$regularRow['loanId']."'
                                      AND isReceive = 0
                                      AND dateOfInstallment < ".$today;
      $selectPandingInterestRes = mysql_query($selectPandingInterest);
      $selectPandingInterestRow = mysql_fetch_array($selectPandingInterestRes); 
      
      if($selectPandingInterestRow['totalPendingInterest'] < 9 )
      {
      	//Select Gold Rate
        $selectGold    = "SELECT goldRate
                            FROM goldrate";
        $selectGoldRes = mysql_query($selectGold);
        if($goldRow = mysql_fetch_array($selectGoldRes))
        {
        	$goldRateCls = $goldRow['goldRate'];
        }
    	  $regularArray[$i]['loanId']           = $regularRow['loanId'];
    	  $regularArray[$i]['accountName']      = $regularRow['accountName'];
    	  $regularArray[$i]['fine']             = $regularRow['fine'];
    	  $regularArray[$i]['gold']             = ($regularRow['gold'] * $goldRateCls) / 10;
    	  $regularArray[$i]['itemlocationName'] = $regularRow['itemlocationName'];
    	  $regularArray[$i]['mobile']           = $regularRow['mobile'];
    	  $regularArray[$i]['pendingAmount']    = $regularRow['pendingAmount'];
    	  $regularArray[$i]['pendingMonth']     = $lastInterestMonthRow['pendingMonth'];
    	  $i++;
      }
    }
  }
  
  //Not Regular Loan Type Process
  if($_POST['selectLoanType'] == "Not_Regular")
  {
  	$selectRegular    = $selectRegular ." WHERE loan.isSold = 'N'";
    $selectRegularRes = mysql_query($selectRegular);
    while($regularRow = mysql_fetch_array($selectRegularRes))
    {
      //Pendding Month Process for Not Regular Loan
  	  $lastInterestMonth       = "SELECT COUNT(isReceive) AS pendingMonth
  	                              FROM interestreceived
  	                             WHERE loanId = '".$regularRow['loanId']."'
  	                               AND isReceive = 0";
  	  $lastInterestMonthResult = mysql_query($lastInterestMonth);
  	  $lastInterestMonthRow    = mysql_fetch_array($lastInterestMonthResult);
  	  
    	//Pendding Interest Process for Not Regular Loan
    	$selectPandingInterest    = "SELECT COUNT(*) AS totalPendingInterest
                                     FROM interestreceived
                                    WHERE loanId = '".$regularRow['loanId']."'
                                      AND isReceive = 0
                                      AND dateOfInstallment < '".$today."' ";
      $selectPandingInterestRes = mysql_query($selectPandingInterest);
      $selectPandingInterestRow = mysql_fetch_array($selectPandingInterestRes);
      if($selectPandingInterestRow['totalPendingInterest'] >= 9 )
      {
        //Select Gold Rate
        $selectGold    = "SELECT goldRate
                            FROM goldrate";
        $selectGoldRes = mysql_query($selectGold);
        if($goldRow = mysql_fetch_array($selectGoldRes))
        {
        	$goldRateCls = $goldRow['goldRate'];
        }
        
    	  $regularArray[$i]['loanId']           = $regularRow['loanId'];
    	  $regularArray[$i]['partyName']        = $regularRow['partyName'];
    	  $regularArray[$i]['fine']             = $regularRow['fine'];
    	  $regularArray[$i]['gold']             = ($regularRow['gold'] * $goldRateCls) / 10;
    	  $regularArray[$i]['itemlocationName'] = $regularRow['itemlocationName'];
    	  $regularArray[$i]['mobile']           = $regularRow['mobile'];
    	  $regularArray[$i]['pendingAmount']    = $regularRow['pendingAmount'];
    	  $regularArray[$i]['pendingMonth']     = $lastInterestMonthRow['pendingMonth'];
    	  $i++;
      }
    }
  }
  
  //Sold Item Process
  if($_POST['selectLoanType'] == "Sold")
  {
    $selectRegular    = $selectRegular." WHERE loan.isSold = 'Y'
                                           AND loan.isDeleted = 'N'";
    $selectRegularRes = mysql_query($selectRegular);
    while($regularRow = mysql_fetch_array($selectRegularRes))
    {
      //Pendding Month Process for Sold Item
  	  $lastInterestMonth       = "SELECT COUNT(isReceive) AS pendingMonth
  	                              FROM interestreceived
  	                             WHERE loanId = '".$regularRow['loanId']."'
  	                               AND isReceive = 0";
  	  $lastInterestMonthResult = mysql_query($lastInterestMonth);
  	  $lastInterestMonthRow    = mysql_fetch_array($lastInterestMonthResult);
  	  
  	  //Select Gold Rate
      $selectGold    = "SELECT goldRate
                          FROM goldrate";
      $selectGoldRes = mysql_query($selectGold);
      if($goldRow = mysql_fetch_array($selectGoldRes))
      {
      	$goldRateCls = $goldRow['goldRate'];
      }
  	  
    	$regularArray[$i]['loanId']           = $regularRow['loanId'];
    	$regularArray[$i]['partyName']        = $regularRow['partyName'];
    	$regularArray[$i]['fine']             = $regularRow['fine'];
    	$regularArray[$i]['gold']             = ($regularRow['gold'] * $goldRateCls) / 10;
    	$regularArray[$i]['itemlocationName'] = $regularRow['itemlocationName'];
    	$regularArray[$i]['mobile']           = $regularRow['mobile'];
    	$regularArray[$i]['pendingAmount']    = $regularRow['pendingAmount'];
    	$regularArray[$i]['soldNarration']    = $regularRow['soldNarration'];
    	$regularArray[$i]['soldDate']         = date('d-m-Y',strtotime($regularRow['soldDate']));
    	$regularArray[$i]['pendingMonth']     = $lastInterestMonthRow['pendingMonth'];
    	$i++;
    }
  }

}
else
{
  //Page Load Process
  $selectRegular    = "SELECT loan.loanId, account.mobile, fine, fine AS gold, rate, account.accountName, itemlocation.itemlocationName, loan.pendingAmount, loan.soldNarration
                         FROM loan
                         JOIN itemlocation ON loan.itemlocationId = itemlocation.itemlocationId
                         JOIN account ON account.accountId = loan.accountId
                        WHERE loan.isSold = 'N'";
  $selectRegularRes = mysql_query($selectRegular);
  while($regularRow = mysql_fetch_array($selectRegularRes))
  {
    //Pendding Month Process
  	$lastInterestMonth       = "SELECT COUNT(isReceive) AS pendingMonth
  	                              FROM interestreceived
  	                             WHERE loanId = '".$regularRow['loanId']."'
  	                               AND isReceive = 0";
  	$lastInterestMonthResult = mysql_query($lastInterestMonth);
  	$lastInterestMonthRow    = mysql_fetch_array($lastInterestMonthResult);
  	
  	//Pendding Interest Process
  	$selectPandingInterest    = "SELECT COUNT(*) AS totalPendingInterest
                                   FROM interestreceived
                                  WHERE loanId = '".$regularRow['loanId']."'
                                    AND isReceive = 0
                                    AND dateOfInstallment < ".$today;
    $selectPandingInterestRes = mysql_query($selectPandingInterest);
    $selectPandingInterestRow = mysql_fetch_array($selectPandingInterestRes);
    if($selectPandingInterestRow['totalPendingInterest'] < 9 )
    {
      //Select Gold Rate
      $selectGold    = "SELECT goldRate
                          FROM goldrate";
      $selectGoldRes = mysql_query($selectGold);
      if($goldRow = mysql_fetch_array($selectGoldRes))
      {
      	$goldRateCls = $goldRow['goldRate'];
      }
        
  	  $regularArray[$i]['loanId']           = $regularRow['loanId'];
  	  $regularArray[$i]['accountName']        = $regularRow['accountName'];
  	  $regularArray[$i]['fine']             = $regularRow['fine'];
  	  $regularArray[$i]['gold']             = ($regularRow['gold'] * $goldRateCls) / 10;
  	  $regularArray[$i]['itemlocationName'] = $regularRow['itemlocationName'];
  	  $regularArray[$i]['mobile']           = $regularRow['mobile'];
  	  $regularArray[$i]['pendingAmount']    = $regularRow['pendingAmount'];
  	  $regularArray[$i]['pendingMonth']     = $lastInterestMonthRow['pendingMonth'];
  	  $i++;
  	}
  }
}

//Select Type of Loan 
$comboSelected   = isset($_REQUEST['selectLoanType']) ? $_REQUEST['selectLoanType'] : "Regular";
$i = 0;
$comboOutput[$i] = "Regular";
$comboValues[$i] = "Regular";
$i++;
$comboOutput[$i] = "Not_Regular";
$comboValues[$i] = "Not_Regular";
$i++;
$comboOutput[$i] = "Sold";
$comboValues[$i] = "Sold";
$i++;
///////////////

//Bottom of the File and Assign Smarty Variable
include("./bottom.php");
$smarty->assign('regularArray',$regularArray); 	
$smarty->assign('comboOutput',$comboOutput); 
$smarty->assign('comboValues',$comboValues); 
$smarty->assign('comboSelected',$comboSelected); 
$smarty->display('soldItem.tpl');
?>